package com.mubea.msf.calcite;

import org.apache.calcite.config.Lex;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.jdbc.CalcitePrepare;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.RelShuttleImpl;
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.sql.*;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.tools.FrameworkConfig;
import org.apache.calcite.tools.Frameworks;
import org.apache.calcite.tools.Planner;
import org.apache.calcite.util.Pair;
import org.apache.calcite.schema.impl.AbstractTable;
import org.apache.calcite.schema.Table;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;

/**
 * @ClassName TestCalcite
 * @Description
 * @Author Neil
 * @Date 2025/6/19 14:26
 * @Version V1.0
 **/
public class TestCalcite {

    public static void main(String[] args) throws Exception {
//        String sql = "SELECT t1.id, t1.name FROM users t1 " +
//                "UNION ALL " +
//                "SELECT t2.id, t2.amount FROM orders t2 " +
//                "UNION ALL " +
//                "SELECT id, name FROM (SELECT t3.id, t3.name FROM users t3 WHERE t3.age > 18) t";
        String sql = "select t1.name, t1.code, t2.name, t2.code from users t1 left join department t2 on t1.dept_id=t2.id";
//        String sql = "select t1.name, t1.code, t2.name, t2.code, t3.name, t3.code from users t1 "
//                + "  inner join department t2 on t1.dept_id=t2.id "
//                + "  inner join organization t3 on t1.org_id=t3.id";
//        String sql = "delete from users t where t.id='123' ";

        // 1. 创建 SQL 解析器
        SqlParser parser = SqlParser.create(sql);
        SqlNode sqlNode = parser.parseQuery();

        // 2. 递归处理所有 SELECT，为每个表加上 deleted = 0 条件
        SqlNode newSqlNode = addConditionToAllSelects(sqlNode, "deleted = 0");
        newSqlNode = addConditionToAllSelects(newSqlNode, "active = 0");

        // 3. 输出新 SQL
        System.out.println(newSqlNode.toSqlString(SqlDialect.DatabaseProduct.ORACLE.getDialect()));
    }

    /**
     * 递归处理所有 SELECT，为每个表加上公共条件
     */
    private static SqlNode addConditionToAllSelects(SqlNode node, String condition) {
        if (node == null){
            return null;
        }
        if (node.getKind() == org.apache.calcite.sql.SqlKind.SELECT) {
            SqlSelect select = (SqlSelect) node;
            // 递归处理 FROM 子句（如子查询）
            SqlNode from = select.getFrom();
            if (from != null) {
                from = addConditionToAllSelects(from, condition);
            }
            // 1. 提取 FROM 子句所有表的别名或表名
            java.util.List<String> aliases = new ArrayList<>();
            extractTableAliases(select.getFrom(), aliases);
            // 2. 为每个别名生成 deleted=0 条件
            java.util.List<SqlNode> conds = new ArrayList<>();
            for (String alias : aliases) {
                String condStr = alias + "." + condition;
                SqlParser parser = SqlParser.create(condStr);
                try {
                    conds.add(parser.parseExpression());
                } catch (Exception e) {
                    throw new RuntimeException("解析条件失败: " + condStr, e);
                }
            }
            // 3. 合并原有 WHERE
            SqlNode where = select.getWhere();
            for (SqlNode cond : conds) {
                if (where == null) {
                    where = cond;
                } else {
                    where = SqlStdOperatorTable.AND.createCall(where.getParserPosition(), where, cond);
                }
            }
            // 构造新的 SELECT
            return new SqlSelect(
                    select.getParserPosition(),
                    null, // keywordList
                    select.getSelectList(),
                    from,
                    where,
                    select.getGroup(),
                    select.getHaving(),
                    select.getWindowList(),
                    select.getOrderList(),
                    select.getOffset(),
                    select.getFetch(),
                    select.getHints()
            );
        } else if (node.getKind() == org.apache.calcite.sql.SqlKind.UNION
                || node.getKind() == org.apache.calcite.sql.SqlKind.INTERSECT
                || node.getKind() == org.apache.calcite.sql.SqlKind.EXCEPT) {
            // 处理 UNION/INTERSECT/EXCEPT
            SqlBasicCall call = (SqlBasicCall) node;
            java.util.List<SqlNode> operands = call.getOperandList();
            SqlNode[] newOperands = new SqlNode[operands.size()];
            for (int i = 0; i < operands.size(); i++) {
                newOperands[i] = addConditionToAllSelects(operands.get(i), condition);
            }
            return call.getOperator().createCall(call.getParserPosition(), newOperands);
        } else if (node.getKind() == org.apache.calcite.sql.SqlKind.AS) {
            // 处理 AS（如子查询别名）
            SqlBasicCall call = (SqlBasicCall) node;
            java.util.List<SqlNode> operands = call.getOperandList();
            SqlNode newLeft = addConditionToAllSelects(operands.get(0), condition);
            return call.getOperator().createCall(call.getParserPosition(), newLeft, operands.get(1));
        }
        // 其它情况直接返回
        return node;
    }

    /**
     * 递归提取 FROM 子句所有表的别名或表名
     */
    private static void extractTableAliases(SqlNode from, java.util.List<String> aliases) {
        if (from == null){
            return;
        }
        switch (from.getKind()) {
            case IDENTIFIER: {
                // 只有表名，无别名
                String table = from.toString();
                aliases.add(table);
                break;
            }
            case AS: {
                // 表有别名
                SqlBasicCall asCall = (SqlBasicCall) from;
                SqlNode aliasNode = asCall.operand(1);
                aliases.add(aliasNode.toString());
                break;
            }
            case JOIN: {
                // JOIN 左右递归
                SqlJoin join = (SqlJoin) from;
                extractTableAliases(join.getLeft(), aliases);
                extractTableAliases(join.getRight(), aliases);
                break;
            }
            case SELECT: {
                // 子查询，忽略
                break;
            }
            default: {
                // 其它情况（如 LATERAL、UNNEST），可根据需要扩展
                break;
            }
        }
    }

}
